package com.lft.freshordering.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lft.freshordering.entity.*;
import com.lft.freshordering.entity.VO.AuditVO;
import com.lft.freshordering.entity.VO.Orders2VO;
import com.lft.freshordering.entity.VO.priceVO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.sql.Blob;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author tyhxzy
 * @since 2024-12-02
 */

/**
 * OrdersMapper接口继承自BaseMapper<Orders>，用于定义与Orders实体相关的SQL查询方法
 */
public interface OrdersMapper extends BaseMapper<Orders> {

    @Select("select id,createtime,price,uid,status,confirm from orders where paystate=0 and status<4 order by createtime desc")
    List<Orders> selectAllOrders();
    @Select("select id,createtime,price,uid,delivery,status,confirm from orders where id=#{0id} ")
    Orders selectOrders(String oid);
    @Select("SELECT goods.goods_name,goods.gjprices,goods_unit.unit_name,order_detail.count from order_detail,goods,stypes,goods_unit WHERE order_detail.sid=goods.goods_id and goods_unit.unit_id=goods.goods_unit and goods.category_id=stypes.id and order_detail.oid=#{oid}")
    List<Map> selectOrderDetail(String oid);
    @Select("select * from audit WHERE user_id=#{uid}")
    Audit selectAudit(Integer id);
    @Select("select id,createtime,price,uid,status,confirm from orders where paystate=0 and status=1 order by createtime asc")
    List<Orders> selectAllOrders1();
    @Select("select id,createtime,price,uid,status,confirm from orders where paystate=0 and status=2 order by createtime asc")
    List<Orders> selectAllOrders2();
    @Update("update orders set status=2 where id=#{id}")
    int updateStatus(Integer id);
    @Update("update orders set delivery=#{delivery} where id=#{id}")
    int updateDelivery(byte[] delivery,Integer id);
    @Update("update orders set status=3 where id=#{id}")
    int updateStatus1(Integer id);


    @Select("SELECT *\n" +
            "FROM `orders`,`order_detail`,`goods`,`audit`,`stypes`\n" +
            "WHERE `orders`.`id`=`order_detail`.`oid`AND order_detail.`sid`=`goods`.`goods_id` AND orders.`uid`=#{uid} order by createtime desc")
    List<Map> selectordersByidzjb(Integer uid);
    @Insert("insert into audit(user_id,status,audit_time) values(#{user_id},#{status},#{audit_time})")
    int insertAudit(Audit audit);



    @Select("SELECT `orders`.`id`,`orders`.`createtime`,`orders`.`price`,`orders`.status,`orders`.confirm\n" +
            "FROM `orders`\n" +
            "WHERE `orders`.`uid`=#{uid}" )
    List<Orders> selectAllOrdersdym(Integer uid);//根据用户id查询用户所有订单

    @Select("SELECT *\n" +
            "FROM `order_detail`\n" +
            "WHERE `order_detail`.`oid`=#{oid}")
    List<OrderDetail> selectOrderDetaildym (String oid);//根据订单id 去订单详细表查询商品的id


    @Select("SELECT *\n" +
            "FROM `goods`\n" +
            "WHERE `goods`.`goods_id`=#{id}")
    Goods selectGoodsByIddym(Integer id);//根据商品id 去商品表查询商品名称



    @Update("UPDATE orders SET status = #{confirmParam} WHERE id = #{idParam}")
    int updateOrderStatusdym(@Param("idParam") String id, @Param("confirmParam") String status);

    @Select("select id,createtime,price,uid,confirm,status from orders where paystate=0 and status=2 and uid=#{uid} order by createtime asc")
    List<Orders> selectOrders1(Integer uid);
    @Select("select id,createtime,price,uid,confirm,status from orders where paystate=0 and status=1 and uid=#{uid} order by createtime asc")
    List<Orders> selectOrders2(Integer uid);


    @Update("UPDATE orders SET status = #{status} WHERE status = '0' AND createtime < #{createTimeThreshold}")
    int autoConfirmOrders(@Param("status") String status, @Param("createTimeThreshold") LocalDateTime createTimeThreshold);


    //zjb向上
//-----------------------------------------------------------------------------------------------------------------------
//ght向下

    //通过商店名查询用户id
    @Select("select user_id from audit where shop_name=#{shopname}")
    Integer selectidbyshopname(String shopname);

    @Select("SELECT SUM(realpay)\n" +
            "FROM orders ")
    Double AllSum();//查询总营收

    @Select("select * \n" +
            "from audit \n")
    List<Audit> selectAll();//查询所有门店账单的基本信息

    //查询门店盈利
    @Select("select  SUM(price)\n" +
            "from orders \n" +
            "where uid = #{id}")
    Double profit(Integer id);

    //查询是否结清
    @Select("SELECT price,realpay FROM `orders` where uid = #{uid}")
    List<priceVO> selectPay(Integer uid);

    @Select("select SUM(price)\n" +
            "from orders\n" +
            "where uid = #{id}")
    Double selectsum(Integer id);//通过用户id查询该店面 累积消费为多少

    @Select("select SUM(realpay)\n" +
            "from orders\n" +
            "where uid = #{id}")
    Double selectrealpay(Integer id);//通过用户id查询该店面 已结清的金额为多少

    @Select("select id, createtime, realpay, price\n" +
            "from orders\n" +
            "where uid = #{id}\n" +
            "order by createtime desc")
    List<Orders2VO> selectsomorders(Integer id);//通过用户id查询用户账单的信息

    //通过用户去订单详细表查询 用户所有的账单的id
    @Select("select id,createtime,realpay\n" +
            "from orders\n" +
            "where uid = #{id}")
    List<OrderDetail> selectodbyoid(String id);

    //拿上订单id去订单详细表中查询商品的id
    @Select("select *\n" +
            "from order_detail\n" +
            "where oid = #{id}")
    List<OrderDetail> selectshopidbyid(String id);

    //拿上商品id去查询具体的商品的名字
    @Select("select goods_name \n" +
            "from goods\n" +
            "where goods_id = #{id}")
    String selectgoodsbyid(Integer id);

    /**
     * 根据用户ID查询该用户的所有订单及其详细信息
     *
     * @param uid 用户ID
     * @return 返回包含用户订单及其详细信息的Map列表
     */
    @Select("SELECT *\n" +
            "FROM `orders`,`order_detail`,`goods`,`audit`,`stypes`\n" +
            "WHERE `orders`.`id`=`order_detail`.`oid`AND order_detail.`sid`=`goods`.`goods_id` AND orders.`uid`=#{uid}")
    List<Map> selectordersByid(Integer uid);

    @Select("SELECT distinct uid \n" +
            "FROM orders\n" +
            "where price = realpay")
    List<Integer> selectweijieqing();//查询已结清的门店信息

    @Select("SELECT * \n" +
            "FROM orders\n" +
            "where price != realpay")
    List<Orders> selectyijieq();//查询未结清的门店信息


 //----------------------------------------------------------------------------------------------------------------------

    /**
     * 更新订单的支付状态
     *
     * 此方法用于在系统中更新一个订单的支付状态当一个订单被用户支付后，
     * 系统需要记录这一变化，以确保订单能够正确处理后续的物流、通知等操作
     *
     * @param id 订单id
     * @return 返回更新操作的结果，通常为受影响的行数，用于进一步的逻辑判断或异常处理
     */
    int updatePaystate(String id);


    /**
     * 更新订单的实际支付金额
     * 此方法用于在系统中更新特定订单的实际支付金额，通常在接收到用户支付信息后调用
     *
     * @param orders 订单对象，包含需要更新的订单信息，如订单ID和新的实际支付金额
     * @return 返回更新操作的结果，通常为受影响的行数，用于验证更新操作是否成功
     */
    int updateRealpayInt(Orders orders);

    /**
     * 插入带有图片的订单
     *
     * @param orders 包含订单详情的对象，包括id, realpay, delivery, status和image字段
     * @return 插入操作影响的行数
     *
     * 此方法使用SQL语句将订单信息插入到Orders表中，包括订单的主键ID、实际支付金额、配送方式、订单状态和订单图片
     */
    @Insert("INSERT INTO Orders (id, realpay, delivery, status, image) VALUES (#{orders.id}, #{orders.realpay}, #{orders.delivery}, #{orders.status}, #{orders.image})")
    int insertOrderWithImage(@Param("orders") Orders orders);

    /**
     * 更新订单的图片信息
     *
     * @param orders 包含订单ID和新的图片信息的对象
     * @return 更新操作影响的行数
     *
     * 此方法使用SQL语句更新Orders表中指定订单的图片信息，通过订单ID来定位需要更新的记录
     */
    @Insert("UPDATE Orders SET image = #{orders.delivery} WHERE id = #{orders.id}")
    int updateOrderImage(@Param("orders") Orders orders);

List<Orders> selectbyid(Integer id);

    @Select("select *\n" +
            "from audit\n" +
            "where user_id = #{uid}")
    AuditVO selectbyuid(Integer uid);

    //新增订单
    @Insert("insert into orders(id,price,uid) values (#{id},#{price},#{uid})")
    void insertneworders(Orders orders);


    @Select("SELECT \n" +
            "    order_detail.count,\n" +
            "    goods.goods_name,\n" +
            "    goods.varietys,\n" +
            "    goods.gjprices,\n" +
            "    goods_unit.unit_name\n" +
            "FROM \n" +
            "    orders\n" +
            "JOIN \n" +
            "    order_detail ON orders.id = order_detail.oid  -- 假设orders表有一个id字段作为主键\n" +
            "JOIN \n" +
            "    goods ON order_detail.sid = goods.goods_id\n" +
            "JOIN \n" +
            "    goods_unit ON goods.goods_unit = goods_unit.unit_id\n" +
            "WHERE \n" +
            "    order_detail.oid =#{oid};\n")
    List<Map> selectOrderDetail1(String oid);
    @Select("SELECT orders.createtime,orders.id,orders.price,audit.shop_name\n" +
            "FROM orders,audit\n" +
            "where orders.uid=audit.user_id AND orders.id=#{id}")
    Map selectOrderDetail2(String oid);


    List<Map> seleclorders11(Map map);

    List<Orders> selectybyid(Integer id);

    @Update("update orders set paystate = #{pid} where id = #{oid}")
    Integer updateOrderPaystate(String oid, String pid);
}

